set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[a_adm_stats_det_sel]	
@RefID nvarchar(10),
@PageSize int,
@PageSetSize int,
@typeDate int, 				-- 0: filter rangeDate			1: filter by specify date form and date to
@rangeDate nvarchar(100), 
@fDateFrom nvarchar(20),
@fDateTo nvarchar(20),
@SortBy nvarchar(50),
@SortOrder nvarchar(4),
@pgNum int
AS
Set NoCount On
SET DATEFIRST 7

if @RefID <> '0'
begin
	
	DECLARE @strSQL nvarchar(3000), @strCriteria nvarchar(1000), @strSort nvarchar(100), @strFilterDate nvarchar(1000), @strFilterStatus nvarchar(100),
		@FirstRec int, @LastRec int, @TotalRecords int, @TotalPages int, @TotalPageSet int, @CurrentSet int,
		@SetLBound int, @SetUBound int, @DateSign nvarchar(50)
	
	declare @atb nvarchar(200), @ssort nvarchar(100), @sstatus nvarchar(100)
	declare @total int, @avgHit int, @RefName nvarchar(100)
	
	set @atb = ''
	SELECT @DateSign = Min(DateVisit) from a_ReferDetail

	Select @RefName = RefName from a_ReferSources where RefID = @RefID
	
	
	if @typeDate = 0 			-- range date
	begin
		-- last 7 days
		if @rangeDate  = 'last7'	
		begin
			set @fDateFrom = convert(varchar(50), getdate() - 6, 101)
			set @fDateTo = convert(varchar(50), getdate() + 1 , 101)
		end
		--	where DateVisit >= convert(varchar(50), getdate() - 6, 101) 	 and DateVisit < convert(varchar(50), getdate() + 1 , 101)
	
		--last 30 days
		else if @rangeDate  = 'last30'	
		begin
			set @fDateFrom = convert(varchar(50), getdate() - 29, 101)
			set @fDateTo = convert(varchar(50), getdate() + 1 , 101)
		end
		--	where DateVisit >= convert(varchar(50), getdate() - 29, 101) 	 and DateVisit < convert(varchar(50), getdate() + 1 , 101)
		
		--last week
		else if @rangeDate  = 'lw'			
		begin
			set @fDateFrom = convert(varchar(50), dbo.fn_getFirstDayWeek (getdate()) - 7, 101)
			set @fDateTo = convert(varchar(50), dbo.fn_getFirstDayWeek (getdate())  , 101)
		end
		--	where DateVisit >= convert(varchar(50), dbo.fn_getFirstDayWeek (getdate()) - 7, 101) 	 and DateVisit < convert(varchar(50), dbo.fn_getFirstDayWeek (getdate())  , 101)
		
		--last month
		else if @rangeDate  = 'lm'			
		begin
			set @fDateFrom = convert(varchar(50), [dbo].[ufn_GetFirstDayOfMonth]([dbo].[ufn_GetFirstDayOfMonth](getdate()) - 1) , 101)
			set @fDateTo = convert(varchar(50), [dbo].[ufn_GetFirstDayOfMonth](getdate()) , 101)
		end
		--	where DateVisit >= convert(varchar(50), [dbo].[ufn_GetFirstDayOfMonth]([dbo].[ufn_GetFirstDayOfMonth](getdate()) - 1) , 101) 	 and DateVisit < convert(varchar(50), [dbo].[ufn_GetFirstDayOfMonth](getdate())  , 101)
		
		-- this week
		else if @rangeDate  = 'tw'			
		begin
			set @fDateFrom = convert(varchar(50), dbo.fn_getFirstDayWeek (getdate()), 101)
			set @fDateTo = convert(varchar(50), getdate() + 1 , 101)
		end
		--	where DateVisit >= convert(varchar(50), dbo.fn_getFirstDayWeek (getdate()), 101) 	 and DateVisit < convert(varchar(50), getdate() + 1 , 101)
		
		---this month
		else if @rangeDate  = 'tm'			
		begin
			set @fDateFrom = convert(varchar(50), [dbo].[ufn_GetFirstDayOfMonth](getdate()) , 101)
			set @fDateTo = convert(varchar(50), getdate() +  1  , 101)
		end
		--where DateVisit >= convert(varchar(50), [dbo].[ufn_GetFirstDayOfMonth](getdate()) , 101) 	 and DateVisit < convert(varchar(50), getdate() +  1  , 101)
	
	end
	else
	begin
			Set @fDateFrom = CAST(@fDateFrom AS datetime) 
			Set @fDateTo = CAST(@fDateTo AS datetime) + 1
	end
	
	
	--Get Search Results
	
		--Sort String
		IF  LEN(@SortBy) = 0
		    Set @strSort = ' Order by DateVisit desc ' --Use default if no sort
		ELSE
		    SET @strSort = ' ORDER BY ' + @SortBy+' '+@SortOrder
		
		--Fiter
		IF Len(@fDateFrom) > 0 AND Len(@fDateTo) > 0 
		    Begin
		    	SET @strFilterDate = ' and (DateVisit >= '''+@fDateFrom+''') AND (DateVisit <'''+@fDateTo+''') '
		    End
		ELSE
		    SET @strFilterDate = ''
	
	
	--Get Search Results end
	
	
	CREATE TABLE #TempList
	(
		[ID] int IDENTITY(1,1),
		DateVisit datetime, 
		TotalHit int
	--	MaxTimeVisit varchar(50)	
	)
	
		--Create a temporary table to implement pagesize	
		SET @strSQL = 'INSERT INTO #TempList (DateVisit, TotalHit) 
						Select DateVisit, count(*) as NumHit
						FROM a_ReferDetail where RefID = '+ @RefID + @strFilterDate + '
						group by DateVisit' + @strSort
	
	
	
	--print @Strsql
	
	EXEC(@strSQL)
	
	select @total = sum(TotalHit) from #TempList
	select @avgHit = avg(TotalHit) from #TempList
			 
		-- Find out the first and last record we want
		SELECT @FirstRec = (@pgNum - 1) * @PageSize
		SELECT @LastRec = (@pgNum * @PageSize + 1)
		
		SELECT @TotalRecords = COUNT(*) FROM #TempList
		
		--Calculate how many pages in this recordset
		If @TotalRecords % @PageSize > 0
		   Select @TotalPages = @TotalRecords/@PageSize + 1
		Else
		   Select @TotalPages = @TotalRecords/@PageSize
		
		--Calculate how many pageset in this recordset 
		If @TotalPages % @PageSetSize > 0	
		   Select @TotalPageSet = @TotalPages/@PageSetSize + 1
		Else
		   Select @TotalPageSet = @TotalPages/@PageSetSize
		
		--Calculate CurrentSet Number
		If @pgNum % @PageSetSize > 0
		   Select @CurrentSet = @pgNum / @PageSetSize + 1
		Else
		   Select @CurrentSet = @pgNum / @PageSetSize
		
		--Calculate Current Set Lower & Uppper Bounds
		Select @SetLBound = (@CurrentSet-1)*@PageSetSize + 1
		Select @SetUBound = @CurrentSet*@PageSetSize
	
	
		--OUTPUT paged values
		IF EXISTS(SELECT 'True' FROM #TempList WHERE ID > @FirstRec AND ID < @LastRec)
		Begin
			SELECT 	@TotalRecords AS TotalRecords, @TotalPages AS TotalPages, @TotalPageSet AS TotalPageSet, 
				@CurrentSet AS CurPgSet, @SetLBound AS SetLBound, @SetUBound AS SetUBound, @PageSetSize AS PageSetSize,
				@PageSize AS PageSize, @DateSign AS DateBegin, @total as totalhit, @avgHit as AvgHit, @RefName as RefName 
	
		    SELECT DateVisit, TotalHit
		    FROM #TempList T 
			WHERE T.ID > @FirstRec AND T.ID < @LastRec
			End
	 	ELSE
		    SELECT 0 as [no result]		




		--Drop Temp table
		DROP TABLE #TempList

end
else
	SELECT 0 as [no result]		



THEEND:










